package com.sample.javabean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.sample.action.form.RoleForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 
 * @author 深深
 * @since 2017.06.02
 * @version 1.0
 * 角色管理类
 *
 */
public class Role {
	private Integer role_no;//角色编号
	private String role_name;//角色名称
	private String role_remark;//角色说明
	private JdbcUtils jdbcUtils = new JdbcUtils();
	//构造函数
	public Role() {
		super();
	}
		public Role(Integer role_no, String role_name, String role_remark) {
			super();
			this.role_no = role_no;
			this.role_name = role_name;
			this.role_remark = role_remark;
		}
		
		/**
		 * 添加角色信息
		 * @param form
		 */
		public void addRole(RoleForm form)throws Exception{
			Connection conn = null;
			Statement stmt = null;
			try {
				conn = jdbcUtils.getConn();
				stmt = conn.createStatement();
				// 3:构造插入SQL
				String sql = "insert into t_role(role_no,role_name,role_remark) values('"
						+ form.getRole_no() + "','" + form.getRole_name() + "','" + form.getRole_remark() +"')";
				// 4:执行SQL并返回结果
				stmt.executeUpdate(sql);
				// 5:关闭资源
			} catch (SQLException e) {
				 
				 System.out.println("role_no="+form.getRole_no()+", role_name="+form.getRole_name()+", role_remark"+form.getRole_remark());
				 e.printStackTrace();
				 throw new Exception("Exception_bean:<--" + e+"->");
			} finally {
				jdbcUtils.close(conn, stmt);
			}	
		}
		
		/**
		 * 删除角色信息
		 */
		public void delRole()throws Exception{
			Connection conn = null;
			Statement stmt = null;
			try {
				conn = jdbcUtils.getConn();
				stmt = conn.createStatement();
				// 3:构造插入SQL
				String sql = "delete from t_role where role_no='"+this.role_no+"'";
				// 4:执行SQL并返回结果
				stmt.executeUpdate(sql);
				// 5:关闭资源
			} catch (SQLException e) {
				 
				System.out.println("role_no = "+this.role_no);
				e.printStackTrace();
				throw new Exception("删除角色失败Exception_bean:<--" +e+"-->");
			} finally {
				jdbcUtils.close(conn, stmt);
			}
			
		}
		
		/**
		 * 按照角色编号加载角色信息
		 * @throws Exception
		 */
		public void load()throws Exception{
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs =null; 
			try {
				conn = jdbcUtils.getConn();
				stmt = conn.createStatement();
				// 3:构造插入SQL
				String sql = "select * from  t_role where role_no='"+this.role_no+"'";
				// 4:执行SQL并返回结果
				rs=stmt.executeQuery(sql);
				if(rs.next()){
					this.role_no =rs.getInt("role_no");
					this.role_name =rs.getString("role_name");
					this.role_remark =rs.getString("role_remark");
				}
				// 5:关闭资源
			} catch (SQLException e) {
				System.out.println("role_no = "+this.role_no+", role_ame = "+this.role_name+". role_remark = "+this.role_remark);
				e.printStackTrace();
				throw new Exception("加载角色信息失败信息失败！Exception_bean:<--"+e+"-->"); 
			} finally {
				jdbcUtils.close(conn, stmt);
			}	
		}
		
		/**
		 * 编辑角色信息
		 * @throws Exception
		 */
		public void editRole(RoleForm form)throws Exception{
			Connection conn = null;
			Statement stmt = null;
			try {
				conn = jdbcUtils.getConn();
				stmt = conn.createStatement();
				if(form.getRole_no()!=null){
				// 3:构造插入SQL
				String sql = "update  t_role set role_name='"+form.getRole_name()+"',role_remark='"+form.getRole_remark()+"' where role_no='"+form.getRole_no()+"'";
				// 4:执行SQL并返回结果
				stmt.executeUpdate(sql);}
				// 5:关闭资源
			} catch (SQLException e) {
				 
				System.out.println("role_no = "+this.role_no+", role_ame = "+this.role_name+". role_remark = "+this.role_remark);
				e.printStackTrace();
				throw new Exception("编辑角色信息失败，Exception_bean:<--"+e+"-->");
				
			} finally {
				jdbcUtils.close(conn, stmt);
			}
		}
		
		public Pager<Role> list(int page,int size,RoleForm roleform)throws Exception {

			Connection conn = null;
			Statement stmt = null;
			ResultSet rs =null;
			Pager<Role> roles=new Pager<Role>();
			roles.setPage(page);
			roles.setSize(size);
			try {
				conn = jdbcUtils.getConn();
				stmt = conn.createStatement();
				int start=(page-1)*size;
				int limit=size;
				//查询SQL
				String selectSql="select * from t_role where 1=1";
				if(roleform!=null&&roleform.getRole_name()!=null&&!"".equals(roleform.getRole_name())){
					selectSql +=" and role_name like'%"+roleform.getRole_name()+"%'";
				}
				//统计SQL
				String countSql ="select count(*)as c from ("+selectSql+") as t1";
				//分页SQL
				 String pageSql=selectSql+" limit "+start+","+limit;
				 rs= stmt.executeQuery(countSql);
				 int total=0;
				 while(rs.next()){
					 total=rs.getInt("c");
				 }
				 roles.setTotal(total);
				 //查询分页信息
				 rs= stmt.executeQuery(pageSql);
				 while(rs.next()){
				
					  int role_no = rs.getInt("role_no");
					  String role_name = rs.getString("role_name");
					  String role_remark = rs.getString("role_remark");
					  Role role = new Role(role_no, role_name, role_remark);
					  roles.getDataSets().add(role);
				 }
				 roles.init();
				// 5:关闭资源
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception("Exception_bean:" +e); 
			} finally {
				jdbcUtils.close(conn, stmt);
			}
			return roles;

		}
		/**
		 * 加载角色名
		 */
		public void loadname()throws Exception{
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs =null; 
			try {
				conn = jdbcUtils.getConn();
				stmt = conn.createStatement();
				// 3:构造插入SQL
				String sql = "select * from  t_role where role_name='"+this.role_name+"'";
				// 4:执行SQL并返回结果
				rs=stmt.executeQuery(sql);
				if(rs.next()){
					this.role_no =rs.getInt("role_no");
					this.role_name =rs.getString("role_name");
					this.role_remark =rs.getString("role_remark");
				}
				// 5:关闭资源
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception("加载角色信息失败！Exception_bean:<--"+e+"-->"); 
			} finally {
				jdbcUtils.close(conn, stmt);
			}	
			
		}
		
		/**
		 * 下拉列表
		 * @return
		 */
		public List<Role> findAll()throws Exception{
			Connection conn=null;
			Statement stmt =null;
			ResultSet rs =null;
			List<Role> roleList =new ArrayList<Role>(20);
			try{
				 conn =jdbcUtils.getConn();
				 stmt =conn.createStatement();
				//构造SQL语句
				String sql="select * from t_role where 1=1";
				 rs =stmt.executeQuery(sql);
				while(rs.next()){
					int role_no = rs.getInt("role_no");
					  String role_name = rs.getString("role_name");
					  String role_remark = rs.getString("role_remark");
					  Role role = new Role(role_no, role_name, role_remark);
					//调用顺序列表的add方法，加入元素
					roleList.add(role);
					
				}
				
			}catch (Exception e) {
				e.printStackTrace();
				throw new Exception("获取角色信息失败，Exception_bean:<--"+e+"-->");
				
			}finally{
				jdbcUtils.close(conn, stmt,rs);
			}
			return roleList;
		}
		
		
		//get/set
		public Integer getRole_no() {
			return role_no;
		}
		public void setRole_no(Integer role_no) {
			this.role_no = role_no;
		}
		public String getRole_name() {
			return role_name;
		}
		public void setRole_name(String role_name) {
			this.role_name = role_name;
		}
		public String getRole_remark() {
			return role_remark;
		}
		public void setRole_remark(String role_remark) {
			this.role_remark = role_remark;
		}
		
		
	

}
